﻿using System.Data;
using System.Data.SqlClient;
using Common.DBUtility;
using Research.IDAL;
using System.Collections.Generic;
using Research.Model;
using System;
using Sucdri.Infrastructure.Core.Extensions;

namespace Research.SQLDAL
{
    public class Basic : IBasic
    {
        private readonly DbHelper _dbHelp = DbFactory.GetDbHelper("ResearchDB");

        public string GetPrjName(string strWhere)
        {
            var cmd = _dbHelp.GetSqlStringCommand("SELECT PrjName FROM vPrjList WHERE PrjNum = @strWhere");
            _dbHelp.AddInParameter(cmd, "@strWhere", DbType.String, strWhere);
            return _dbHelp.ExecuteScalar<string>(cmd);
        }

        public DataSet GetDepList()
        {
            return _dbHelp.ExecuteDataSet("SELECT DepID,DepartName FROM vCostDep ORDER BY DepSort ");
        }

        public DataSet GetSubjectList()
        {
            return _dbHelp.ExecuteDataSet("SELECT SubjectID,SubjectName FROM S_Subject");
        }

        public DataSet GetPlatformList()
        {
            return _dbHelp.ExecuteDataSet("SELECT PlatformID,PlatformName FROM S_Platform");
        }

        public DataSet GetRprjRoleList()
        {
            return _dbHelp.ExecuteDataSet("SELECT RprjRoleID,RoleName FROM S_RprjRole");
        }

        public DataSet GetFundList()
        {
            return _dbHelp.ExecuteDataSet("SELECT FundID,FundName FROM S_Fund");
        }

        public DataSet GetRprjData(int rPrjID, int dataTypeID)
        {
            return
                _dbHelp.ExecuteDataSet(
                    "SELECT RprjID,DataTypeID,DataName,DataPath,UploadDate FROM RprjData WHERE RprjID=" +
                    rPrjID.ToString() + " and DataTypeID=" + dataTypeID.ToString());
        }

        public List<MRprjChargePer> GetChargePerList(string strWhere, string topNum)
        {
            var list = new List<MRprjChargePer>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    string.Format(
                        "SELECT {0} LZID,LoginName,UserName,DepartName FROM vLoginUser LEFT JOIN vCostDep ON vLoginUser.CostDepID=vCostDep.DepID WHERE UserName like @strWhere",
                        topNum));
            _dbHelp.AddInParameter(cmd, "@strWhere", DbType.String, strWhere);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjChargePer
                                 {
                                     ChargePerID = idr.Retrieve<string>("aaaa"),
                                     LoginName = idr.GetString(1),
                                     ChargePerName = idr.GetString(2),
                                     DepartName = idr.GetString(3)
                                 });
                }
            }
            return list;
        }

        public List<MRprjPrj> GetPrjList(string strWhere)
        {
            var list = new List<MRprjPrj>();
            var cmd =
                _dbHelp.GetSqlStringCommand("SELECT top 1 PrjID,PrjNum,PrjName FROM vPrjList WHERE PrjNum = @strWhere");
            _dbHelp.AddInParameter(cmd, "@strWhere", DbType.String, strWhere);
          
            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                   
                    list.Add(new MRprjPrj
                                 {
                                     PrjID = idr.GetString(0),
                                     PrjNum = idr.GetString(1),
                                     PrjName = idr.GetString(2)
                                 });
                }
            }
            return list;
        }

        public List<MRprjPlanSchedule> GetPlanScheduleList(int rPrjID)
        {
            var list = new List<MRprjPlanSchedule>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"select AutoNum, ScheduleName,ScheduleSort,PlanBegDate,PlanEndDate, PlanMemo,ISNULL((SELECT max(Percentage) FROM RprjActualSchedule WHERE RprjID=RprjSchedule.RprjID AND AutoNum=RprjSchedule.AutoNum),0.0) AS FinishPercentage
                                                    from RprjSchedule
                                                    where RprjID=@RprjID order by ScheduleSort");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjPlanSchedule
                                 {
                                     AutoNum = idr.GetInt32(0),
                                     ScheduleName = idr.GetString(1),
                                     ScheduleSort = idr.GetInt32(2),
                                     PlanBegDate = idr.GetDateTime(3),
                                     PlanEndDate =  idr.GetDateTime(4),
                                     PlanMemo = idr.IsDBNull(5) ? string.Empty : idr.GetString(5),
                                     FinishPercentage= idr.GetDouble(6)
                                 });
                }
            }
            return list;
        }

        public List<MRprjActualSchedule> GetActualScheduleList(MRprjPlanSchedule mPlanSchedule)
        {
            var list = new List<MRprjActualSchedule>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, AutoNum,RegDate,Percentage
                                                    FROM RprjActualSchedule
                                                    WHERE RprjID=@RprjID AND AutoNum=@AutoNum order by RegDate");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, mPlanSchedule.RprjID);
            _dbHelp.AddInParameter(cmd, "@AutoNum", DbType.Int32, mPlanSchedule.AutoNum);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjActualSchedule
                                 {
                                     RprjID = idr.GetInt32(0),
                                     AutoNum = idr.GetInt32(1),
                                     RegDate = idr.GetDateTime(2),
                                     Percentage = idr.GetDouble(3)
                                 });
                }
            }
            return list;
        }

        public List<MRprjDep> GetRprjDepsById(int rPrjID)
        {
            var list = new List<MRprjDep>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, DepID
                                                    FROM RprjDep
                                                    WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjDep
                                 {
                                     DepID = idr.GetInt32(1)
                                 });
                }
            }

            return list;
        }

        public List<MRprjPrj> GetRprjPrjsById(int rPrjID)
        {
            var list = new List<MRprjPrj>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, RprjProject.PrjID,PrjNum,PrjName 
                                                    FROM RprjProject left join vPrjList on RprjProject.PrjID=vPrjList.PrjID
                                                    WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjPrj
                                 {
                                     PrjID = idr.GetString(1),
                                     PrjNum = idr.GetString(2),
                                     PrjName = idr.GetString(3)
                                 });
                }
            }

            return list;
        }

        public List<MRprjSubject> GetRprjSubjsById(int rPrjID)
        {
            var list = new List<MRprjSubject>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, RprjSubjectID
                                                    FROM RprjSubject
                                                    WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjSubject
                                 {
                                     RprjSubjectID = idr.GetInt32(1)
                                 });
                }
            }

            return list;
        }

        public List<MRprjFund> GetRprjFundsById(int rPrjID)
        {
            var list = new List<MRprjFund>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, RprjFund.FundID, FundName, Amount,FundDate,Memo 
                                                    FROM RprjFund left join S_Fund on RprjFund.FundID=S_Fund.FundID
                                                    WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjFund
                                 {
                                     FundID = idr.GetInt32(1),
                                     FundName = idr.GetString(2),
                                     Amount = Convert.ToDouble(idr.GetDecimal(3)),
                                     AddDate = idr.IsDBNull(4) ? null : idr.GetDateTime(4).ToString("yyyy-MM-dd"),
                                     Memo = idr.IsDBNull(5) ? string.Empty : idr.GetString(5)
                                 });
                }
            }

            return list;
        }

        public List<MRprjChargePer> GetRprjChargePersById(int rPrjID)
        {
            var list = new List<MRprjChargePer>();
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, ChargePer, UserName FROM RprjChargePer
                                                    left join vLoginUser on RprjChargePer.ChargePer=vLoginUser.LZID
                                                    WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                while (idr.Read())
                {
                    list.Add(new MRprjChargePer
                                 {
                                     ChargePerID = idr.GetString(1),
                                     ChargePerName = idr.GetString(2)
                                 });
                }
            }

            return list;
        }

        public MRprjMain GetRprjMainById(int rPrjID)
        {
            MRprjMain mRprjMain;
            var cmd =
                _dbHelp.GetSqlStringCommand(
                    @"SELECT RprjID, RprjNum, RprjName,RprjPlatformID,RprjRoleID FROM RprjMain                                                   
                                                    WHERE RprjID=@RprjID");
            _dbHelp.AddInParameter(cmd, "@RprjID", DbType.Int32, rPrjID);

            using (var idr = _dbHelp.ExecuteReader(cmd))
            {
                idr.Read();
                mRprjMain = new MRprjMain
                                {
                                    RprjID = idr.GetInt32(0),
                                    RprjNum = idr.GetString(1),
                                    RprjName = idr.GetString(2),
                                    RprjPlatformID = idr.GetInt32(3),
                                    RprjRoleID = idr.GetInt32(4),
                                    ChargePers = GetRprjChargePersById(rPrjID),
                                    Deps = GetRprjDepsById(rPrjID),
                                    Funds = GetRprjFundsById(rPrjID),
                                    Prjs = GetRprjPrjsById(rPrjID),
                                    Subjects = GetRprjSubjsById(rPrjID)
                                };
            }

            return mRprjMain;
        }
    }
}